from sqlalchemy import func, or_, and_, exc
from collections import defaultdict

from app.config.db import DBUtils
from app.model.BusinessInfoModel import BusinessInfo
from app.model.FKListModel import fklist
from app.model.SKListModel import sklist
from app.timetest.timing_decorator import timing_decorator

db_utils = DBUtils()

class OpponentCharactersService:

    def __init__(self):
        self.db = DBUtils()

    # 对手特征
    @timing_decorator
    def select_dstz_data(self, session, companyName, company_type, start_date, end_date):
        if companyName is not None:
            # 成员单位
            if company_type == 1:
                try:
                    # 上游
                    # sy_data = session.query(fklist.SKZHMC, func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    #     fklist.FKZHMC == companyName,
                    #     fklist.SCRQ >= start_date,  # 起始日期
                    #     fklist.SCRQ <= end_date  # 截止日期
                    # ).group_by(fklist.SKZHMC).all()
                    sy_data = session.query(fklist.SKZHMC, func.sum(fklist.FKJE)).select_from(fklist).filter(
                        fklist.FKZHMC == companyName,
                        fklist.SKZHMC != '待报解预算收入（财库联网集中户）',
                        fklist.SKZHMC != '批量账务集中处理',
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).group_by(fklist.SKZHMC).all()

                    # 下游
                    # xy_data = session.query(sklist.FKZHMC, func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    #     sklist.SKZHMC == companyName,
                    #     sklist.SCRQ >= start_date,  # 起始日期
                    #     sklist.SCRQ <= end_date  # 截止日期
                    # ).group_by(sklist.FKZHMC).all()
                    xy_data = session.query(sklist.FKZHMC, func.sum(sklist.FKJE)).select_from(sklist).filter(
                        sklist.SKZHMC == companyName,
                        sklist.FKZHMC != '待报解预算收入（财库联网集中户）',
                        sklist.FKZHMC != '批量账务集中处理',
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).group_by(sklist.FKZHMC).all()
                except exc.SQLAlchemyError as e:
                    error_response = [{'error': str(e)}]
                    try:
                        db_utils.rollback()
                    except Exception as rollback_error:
                        print(f"Failed to rollback transaction: {rollback_error}")
                    return error_response
                finally:
                    try:
                        db_utils.remove_session()
                    except Exception as remove_error:
                        print(f"Failed to rollback transaction: {remove_error}")
                sy_cl_data = [item for item in sy_data if item != (None, 0)]
                xy_cl_data = [item for item in xy_data if item != (None, 0)]
                sy_list = [{key: value} for key, value in sy_cl_data]
                xy_list = [{key: value} for key, value in xy_cl_data]
                merged_data = defaultdict(int)
                for data in sy_list + xy_list:
                    for key, value in data.items():
                        merged_data[key] += value
                sorted_data = sorted(merged_data.items(), key=lambda x: x[1], reverse=True)
                sxy_list = [{key: value} for key, value in sorted_data]
                max_company,max_je = next(iter(sxy_list[0].items())) if sxy_list else (None,0) # 判断sxy_list是否为空，为空拿不到值
                print(max_je)
                sum_je = sum(value for item in sxy_list for value in item.values()) if sxy_list else 0 # 判断sxy_list是否为空，为空拿不到值
                print(sum_je)
                max_data = None
                if max_company is not None:
                    try:
                        max_data = session.query(BusinessInfo.QYGM,BusinessInfo.SSSF,BusinessInfo.GBHYML).filter(BusinessInfo.GSMC == max_company).first()
                    except exc.SQLAlchemyError as e:
                        error_response = [{'error': str(e)}]
                        try:
                            session.rollback()
                        except Exception as rollback_error:
                            print(f"Failed to rollback transaction: {rollback_error}")
                        return error_response
                    finally:
                        try:
                            db_utils.remove_session()
                        except Exception as remove_error:
                            print(f"Failed to rollback transaction: {remove_error}")
                if max_data is not None:
                    qygm,sssf,sjhy = max_data
                else:
                    qygm,sssf,sjhy = (None,None,None)
                result = {
                    "qygm": qygm,
                    "jezb": str(round((max_je/sum_je)*100,2) if sum_je != 0 else 0)+'%', # 判断sum_je是否为0，如果为0则不计算
                    "sssf": sssf,
                    "sjhy": sjhy
                }
            else:
                result = None
        else:
            result = None
        return result

    # 对手规模情况&成员单位分布
    @timing_decorator
    def select_dsgm_cydw_data(self, session, companyName, company_type, start_date, end_date):
        if companyName is not None:
            sy_data = []
            xy_data = []
            try:
                if company_type == 0: # 往来单位
                    # 上游
                    sy_data = session.query(sklist.FKGS,func.count(sklist.FKGS)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        sklist.SKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).group_by(sklist.FKGS).all()
                    # 下游
                    xy_data = session.query(fklist.SKGS,func.count(fklist.SKGS)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        fklist.FKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).group_by(fklist.SKGS).all()
                elif company_type == 1: # 成员单位
                    # 上游
                    sy_data = session.query(BusinessInfo.QYGM,func.count(BusinessInfo.QYGM)).select_from(fklist).outerjoin(BusinessInfo,fklist.FKZHMC == BusinessInfo.GSMC).filter(
                        fklist.SKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.QYGM).all()
                    # 下游
                    xy_data = session.query(BusinessInfo.QYGM,func.count(BusinessInfo.QYGM)).select_from(sklist).outerjoin(BusinessInfo,sklist.SKZHMC == BusinessInfo.GSMC).filter(
                        sklist.FKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.QYGM).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            sy_bz_data = [item for item in sy_data if item[0] not in ('-', None)]
            xy_bz_data = [item for item in xy_data if item[0] not in ('-', None)]
            sy_list = [{key: value} for key, value in sy_bz_data]
            xy_list = [{key: value} for key, value in xy_bz_data]
            all_keys = {list(d.keys())[0] for d in sy_list + xy_list}
            sy_normalized_list = []
            for key in all_keys:
                value = next((list(d.values())[0] for d in sy_list if key in d), 0)
                sy_normalized_list.append({
                        'name': key,
                        'value': value
                })
            xy_normalized_list = []
            for key in all_keys:
                value = next((list(d.values())[0] for d in xy_list if key in d), 0)
                xy_normalized_list.append({
                    'name': key,
                    'value': value
                })
            # 计算上游总数
            sy_num = sum(value for key,value in sy_data)
            # 计算下游总数
            xy_num = sum(value for key, value in xy_data)
            # 计算上游占比
            # sy_ratio_list = []
            # for item in sy_list:
            #     sy_ratio = {}
            #     for key,value in item.items():
            #         if key not in ('-',None):
            #             sy_ratio[key] = str(round((value/sy_num)*100,2)) + '%'
            #         else:
            #             continue
            #     sy_ratio_list.append(sy_ratio)
            sy_num_list = []
            for item in sy_list:
                sy_mg = {}
                for key, value in item.items():
                    if key not in ('-', None):
                        sy_mg['name'] = key
                        sy_mg['value'] = value
                    else:
                        continue
                sy_num_list.append(sy_mg)
            # 计算下游占比
            # xy_ratio_list = []
            # for item in xy_list:
            #     xy_ratio = {}
            #     for key, value in item.items():
            #         if key not in ('-', None):
            #             xy_ratio[key] = str(round((value / xy_num) * 100, 2)) + '%'
            #         else:
            #             continue
            #     xy_ratio_list.append(xy_ratio)
            xy_num_list = []
            for item in xy_list:
                xy_mg = {}
                for key, value in item.items():
                    if key not in ('-', None):
                        xy_mg['name'] = key
                        xy_mg['value'] = value
                    else:
                        continue
                xy_num_list.append(xy_mg)
            # 上下游饼图
            sxy_pie_chart = {
                'sy_num': sy_num, # 上游总数
                'sy_num_list': sy_num_list, # 上游占比
                'xy_num': xy_num, # 下游总数
                'xy_num_list': xy_num_list # 下游占比
            }
            # 上下游数量统计
            sxy_statistics = {
                'all_keys': list(all_keys),
                'sy_normalized_list': sy_normalized_list, # 上游
                'xy_normalized_list': xy_normalized_list # 下游
            }
            # 分布
            cydw_info = {
                'sxy_pie_chart': sxy_pie_chart,  # 上下游饼图
                'sxy_statistics' : sxy_statistics # 上下游数量统计
            }
        else:
            cydw_info = None
        result = {
            'cydw_info' : cydw_info
        }
        return result

    # 对手地域分布
    @timing_decorator
    def select_dyfb_data(self, session, companyName, company_type, start_date, end_date):
        # 定义中国所有省份的列表
        provinces = ['黑龙江','吉林','辽宁','内蒙古','河北','北京','天津','新疆','西藏','青海','甘肃','宁夏','山西','山东','陕西','河南','江苏','四川','重庆','湖北','安徽','上海','贵州','湖南','江西','浙江','云南','广西','广东','福建','海南','澳门','香港','台湾','南海诸岛']
        if companyName is not None:
            # 全国分布
            qgfb_data = []
            sy_data = None
            xy_data = None
            try:
                if company_type == 1:
                    # 获取对手省份分布
                    # 上游
                    sy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(fklist).outerjoin(BusinessInfo,fklist.FKZHMC == BusinessInfo.GSMC).filter(
                        fklist.SKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.SSSF).all()
                    # 下游
                    xy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(sklist).outerjoin(BusinessInfo,sklist.SKZHMC == BusinessInfo.GSMC).filter(
                        sklist.FKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.SSSF).all()
                else:
                    # 上游
                    sy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(sklist).outerjoin(
                        BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        sklist.SKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.SSSF).all()
                    # 下游
                    xy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(fklist).outerjoin(
                        BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        fklist.FKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.SSSF).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            sy_sssf_data = [item for item in sy_data if item[0] not in ('-', None)]
            xy_sssf_data = [item for item in xy_data if item[0] not in ('-', None)]
            if len(sy_sssf_data) == 0 and len(xy_sssf_data) == 0:
                qgfb_data = []
                top5_sf = []
                top5_num = []
            else:
                merged_data = {}
                for province, count in sy_sssf_data:
                    if province in merged_data:
                        merged_data[province] += count
                    else:
                        merged_data[province] = count
                for province, count in xy_sssf_data:
                    if province in merged_data:
                        merged_data[province] += count
                    else:
                        merged_data[province] = count
                sssf_data = [{province: count} for province, count in merged_data.items()]
                for province in provinces:
                    fb = {'name': province, 'value': 0}  # 初始化字典，默认值为 0
                    for item in sssf_data:
                        for key, value in item.items():
                            if province in key:
                                fb['value'] = value  # 如果匹配到，更新 value 的值
                                break
                    qgfb_data.append(fb)  # 将 fb 添加到结果列表
                sorted_data = sorted(qgfb_data, key=lambda x: x['value'], reverse=True)
                # TOP5省份
                top5_data = sorted_data[:5]
                top5_sf = []
                top5_num = []
                for item in top5_data:
                    top5_sf.append(item['name'])
                    top5_num.append(item['value'])
            # 对手地域分布
            dsdy_info = {
                'qgfb_data': qgfb_data,
                'top5_sf': top5_sf,
                'top5_num': top5_num
            }
            result = {
                'dsdy_info' : dsdy_info # 对手地域分布
            }
        else:
            result = None
        return result

    # 对手行业分布
    @timing_decorator
    def select_hyfb_data(self, session, companyName, company_type, start_date, end_date):
        if companyName is not None:
            sy_data = None
            xy_data = None
            try:
                if company_type == 1:
                    # 获取对手行业分布
                    # 上游
                    sy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(fklist).outerjoin(BusinessInfo,fklist.FKZHMC == BusinessInfo.GSMC).filter(
                        fklist.SKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.GBHYML).all()
                    # 下游
                    xy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(sklist).outerjoin(BusinessInfo,sklist.SKZHMC == BusinessInfo.GSMC).filter(
                        sklist.FKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.GBHYML).all()
                else:
                    # 获取对手行业分布
                    # 上游
                    sy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(
                        sklist).outerjoin(BusinessInfo, sklist.SKZHMC == BusinessInfo.GSMC).filter(
                        sklist.FKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.GBHYML).all()
                    # 下游
                    xy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(
                        fklist).outerjoin(BusinessInfo, fklist.FKZHMC == BusinessInfo.GSMC).filter(
                        fklist.SKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).group_by(BusinessInfo.GBHYML).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            sy_gbhyml_data = [item for item in sy_data if item[0] not in (None, 0, '-')]
            xy_gbhyml_data = [item for item in xy_data if item[0] not in (None, 0, '-')]
            sy_gbhyml_list = [{key: value} for key, value in sy_gbhyml_data]
            xy_gbhyml_list = [{key: value} for key, value in xy_gbhyml_data]
            all_keys = {list(d.keys())[0] for d in sy_gbhyml_list + xy_gbhyml_list}
            sy_normalized_list = []
            if len(sy_gbhyml_list) == 0:
                sy_normalized_list = []
            else:
                for key in all_keys:
                    value = next((list(d.values())[0] for d in sy_gbhyml_list if key in d), 0)
                    sy_normalized_list.append({
                        'name': key,
                        'value': value
                    })
            xy_normalized_list = []
            if len(xy_gbhyml_list) == 0:
                xy_normalized_list = []
            else:
                for key in all_keys:
                    value = next((list(d.values())[0] for d in xy_gbhyml_list if key in d), 0)
                    xy_normalized_list.append({
                        'name': key,
                        'value': value
                    })
            # 计算上游总数
            sy_gbhyml_num = sum(value for key, value in sy_gbhyml_data)
            # 计算下游总数
            xy_gbhyml_num = sum(value for key, value in xy_gbhyml_data)
            # 计算上游占比
            # sy_gbhyml_ratio_list = []
            # for item in sy_gbhyml_list :
            #     sy_ratio = {}
            #     for key, value in item.items():
            #         if key not in ('-', None):
            #             sy_ratio[key] = str(round((value / sy_gbhyml_num) * 100, 2)) + '%'
            #         else:
            #             continue
            #     sy_gbhyml_ratio_list.append(sy_ratio)
            sy_gbhyml_num_list = []
            for item in sy_gbhyml_list:
                sy_mg = {}
                for key, value in item.items():
                    if key not in ('-', None):
                        sy_mg['name'] = key
                        sy_mg['value'] = value
                    else:
                        continue
                sy_gbhyml_num_list.append(sy_mg)
            # 计算下游占比
            # xy_gbhyml_ratio_list = []
            # for item in xy_gbhyml_list:
            #     xy_ratio = {}
            #     for key, value in item.items():
            #         if key not in ('-', None):
            #             xy_ratio[key] = str(round((value / xy_gbhyml_num) * 100, 2)) + '%'
            #         else:
            #             continue
            #     xy_gbhyml_ratio_list.append(xy_ratio)
            xy_gbhyml_num_list = []
            for item in xy_gbhyml_list:
                xy_mg = {}
                for key, value in item.items():
                    if key not in ('-', None):
                        xy_mg['name'] = key
                        xy_mg['value'] = value
                    else:
                        continue
                xy_gbhyml_num_list.append(xy_mg)
            sy_gbhyml_num_list = [item for item in sy_gbhyml_num_list if item]
            xy_gbhyml_num_list = [item for item in xy_gbhyml_num_list if item]
            # 上下游饼图
            sxy_pie_chart = {
                'sy_gbhyml_num': sy_gbhyml_num,  # 上游总数
                'sy_gbhyml_num_list': sy_gbhyml_num_list,  # 上游占比
                'xy_gbhyml_num': xy_gbhyml_num,  # 下游总数
                'xy_gbhyml_num_list': xy_gbhyml_num_list  # 下游占比
            }
            # 上下游数量统计
            sxy_statistics = {
                'all_keys': list(all_keys),
                'sy_normalized_list': sy_normalized_list,  # 上游
                'xy_normalized_list': xy_normalized_list  # 下游
            }
            # 分布
            dshy_info = {
                'sxy_pie_chart': sxy_pie_chart,  # 上下游饼图
                'sxy_statistics': sxy_statistics  # 上下游数量统计
            }
            result = {
                'dshy_info': dshy_info
            }
        else:
            result = None
        return result


